package com.newbee.sqlite;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.newbee.listbean.ChatMsgBean;
import com.newbee.listbean.FriendsAskBean;
import com.newbee.listbean.InviteMultBean;
import com.newbee.listbean.MultResultBean;
import com.newbee.listbean.NewMsgBean;
import com.newbee.listbean.VoiceFileBean;
import com.newbee.listbean.NewMsgBean.MsgType;
import com.newbee.listbean.NewMsgBean.Status;
import com.newbee.smack.SmackClient;
import com.newbee.util.MD5Util;
import com.newbee.util.StringUtil;

public class DBHelper extends SQLiteOpenHelper
{

	// 数据库版本
	private static final int VERSION = 1;
	public static final String TABLE_NAME = "VoiceRegContent";
	public static final String ID = "vid";
	public static final String TIME = "time";
	public static final String CONTENT = "content";
	// table
	public static final String ASK_FOR_FRIEND_TABLE_NAME = "AskForFriends";
	public static final String ASK_FOR_FRIEND_TABLE_ID = "fid";
	public static final String ASK_FOR_FRIEND_TABLE_FROM = "fromfriend";
	public static final String ASK_FOR_FRIEND_TABLE_TO = "tofriend";
	public static final String ASK_FOR_FRIEND_TABLE_STATUS = "fstatus";
	public static final String ASK_FOR_FRIEND_TABLE_TAG = "ftag";
	// table
	public static final String MULT_CHAT_INVITE_TABLE_NAME = "InviteMultChat";
	public static final String MULT_CHAT_INVITE_TABLE_ID = "mcitid";
	public static final String ROOM_NAME = "roomname";
	public static final String MULT_CHAT_INVITE_TIME = "itime";
	public static final String MULT_CHAT_INVITE_STATUS = "itag";

	// table
	public static final String MULT_CNAT_INFO_TABLE_NAME = "multchatinfo";
	public static final String MULT_CNAT_INFO_ID = "minfoid";
	public static final String MULT_CNAT_INFO_CONFERE_ROOM_NAME = "minfroom";
	public static final String MULT_CNAT_INFO_MESSAGE = "multmessage";
	public static final String MULT_CNAT_INFO_FROM_TO = "multmessagestatus";
	public static final String MULT_CHAT_INFO_FROM = "multfrom";
	public static final String MULT_CHAT_INFO_TIME = "mtime";

	// 新建一个表
	private static final String SOLO_VOICE_SQL = "create table if not exists "
			+ TABLE_NAME + " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ TIME + " varchar(20), " + CONTENT + " varchar(5000))";

	private static final String ASK_FOR_FRIEND_SQL = "create table if not exists "
			+ ASK_FOR_FRIEND_TABLE_NAME
			+ " ("
			+ ASK_FOR_FRIEND_TABLE_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ ASK_FOR_FRIEND_TABLE_FROM
			+ " varchar(100), "
			+ ASK_FOR_FRIEND_TABLE_TO
			+ " varchar(100), "
			+ ASK_FOR_FRIEND_TABLE_STATUS
			+ " int ,"
			+ ASK_FOR_FRIEND_TABLE_TAG
			+ " int  )";

	private static final String MULT_CHAT_INVITE_SQL = "create table if not exists "
			+ MULT_CHAT_INVITE_TABLE_NAME
			+ " ("
			+ MULT_CHAT_INVITE_TABLE_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ ROOM_NAME
			+ " varchar(200), "
			+ MULT_CHAT_INVITE_TIME
			+ " varchar(200) , "
			+ MULT_CHAT_INVITE_STATUS + " int )";

	private static final String MULT_CHAT_MESSAGE_SQL = "create table if not exists "
			+ MULT_CNAT_INFO_TABLE_NAME
			+ " ("
			+ MULT_CNAT_INFO_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , "
			+ MULT_CNAT_INFO_CONFERE_ROOM_NAME
			+ " varchar(200), "
			+ MULT_CHAT_INFO_FROM
			+ " varchar(200), "
			+ MULT_CNAT_INFO_MESSAGE
			+ " varchar(200) , "
			+ MULT_CHAT_INFO_TIME
			+ " varchar(100) ,"
			+ MULT_CNAT_INFO_FROM_TO + " int )";

	private static final String VOICE_FILE_TABLE_NAME = "voicefile";
	private static final String VOICE_ID = "vid";
	private static final String VOICE_FILE_NAME = "vfname";
	private static final String VOICE_FILE_TIME = "vftime";
	private static final String VOICE_FILE_TRANS_NAME = "transout";
	private static final String VOICE_FILE_CONTENT = "content";
	private static final String VOICE_LENGTH = "vlength";

	private static final String VOICE_TYPE = "Vtype";

	private static final String VOICE_FILE_SQL = "create table if not exists "
			+ VOICE_FILE_TABLE_NAME + " (" + VOICE_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , " + VOICE_FILE_NAME
			+ " varchar(200), " + VOICE_FILE_TIME + " varchar(200), "
			+ VOICE_FILE_TRANS_NAME + " varchar(200) , " + VOICE_FILE_CONTENT
			+ " varchar(100) ," + VOICE_LENGTH + " varchar(200),"
			+ VOICE_TYPE + " int )";

	private static final String MULT_RESULT_TABLE_NAME = "mltresult";
	private static final String MULT_RESULT_ID = "mltid";
	private static final String MULT_RESULT_QZ = "mltqz";
	private static final String MULT_RESULT_FILE = "mltfile";
	private static final String MULT_RESULT_TIME = "mlttime";

	private static final String MULT_RESULT_SQL = "create table if not exists "
			+ MULT_RESULT_TABLE_NAME + " (" + MULT_RESULT_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , " + MULT_RESULT_QZ
			+ " varchar(200), " + MULT_RESULT_FILE + " varchar(200), "
			+ MULT_RESULT_TIME + " varchar(200)  )";

	/******************************************/

	private static final String NEW_MSG_TABLE = "newmsg";

	private static final String NEW_MSG_id = "NEW_MSG_id";//
	private static final String NEW_MSG_userid = "userid";// 用户的id，此时可能为null
	private static final String NEW_MSG_head = "head";// head

	private static final String NEW_MSG_fromFriend = "fromFriend";
	private static final String NEW_MSG_toFriend = "toFriend";

	private static final String NEW_MSG_room = "room";// 房间

	private static final String NEW_MSG_msgtype = "msgtype";// 消息的类型

	private static final String NEW_MSG_time = "time";

	private static final String NEW_MSG_msgStatus = "msgStatus";

	private static final String New_Msg_SQL = "create table if not exists "
			+ NEW_MSG_TABLE + " (" + NEW_MSG_id
			+ " INTEGER PRIMARY KEY AUTOINCREMENT , " + NEW_MSG_userid
			+ " varchar(200), " + NEW_MSG_head + " varchar(200), "
			+ NEW_MSG_fromFriend + " varchar(200), " + NEW_MSG_toFriend
			+ " varchar(200), " + NEW_MSG_room + " varchar(200), "
			+ NEW_MSG_msgtype + " int , " + NEW_MSG_time + " varchar(200), "
			+ NEW_MSG_msgStatus + " int  )";

	/***************************************************/
	private static final String NAME = "Voice";

	public static int ASK_FRIEND = 0;
	public static int REFUSE_FRIEND = 1;
	public static int UN_SOLVE = 1;
	public static int SOLVE = 2;

	public static int COME = 1;// 别热发给你的消息啦
	public static int TO = 2;// 往外发送的消息啦

	private SQLiteDatabase mDB;

	public void insertNewFriendAskMsg(String from, String to)
	{
		mDB = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(NEW_MSG_userid, SmackClient.getInstance().getAccount());
		values.put(NEW_MSG_head, from.split("\\$")[2].split("@")[0]);
		values.put(NEW_MSG_fromFriend, from);
		values.put(NEW_MSG_toFriend, to);
		values.put(NEW_MSG_room, "null");
		values.put(NEW_MSG_msgtype, MsgType.FRIRND);
		values.put(NEW_MSG_time, MD5Util.getNowTime().substring(0, 11));
		values.put(NEW_MSG_msgStatus, Status.UNSOLVE);
		mDB.insert(NEW_MSG_TABLE, null, values);
		mDB.close();
	}

	public void insertNewMultChatInvite(String room)
	{
		mDB = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(NEW_MSG_userid, SmackClient.getInstance().getAccount());
		values.put(NEW_MSG_head, "null");
		values.put(NEW_MSG_fromFriend, "null");
		values.put(NEW_MSG_toFriend, "null");
		values.put(NEW_MSG_room, room);
		values.put(NEW_MSG_msgtype, MsgType.QUNZU);
		values.put(NEW_MSG_time, MD5Util.getNowTime().substring(0, 11));
		values.put(NEW_MSG_msgStatus, Status.UNSOLVE);
		mDB.insert(NEW_MSG_TABLE, null, values);
	}

	public DBHelper(Context context, String name, CursorFactory factory,
			int version)
	{
		super(context, name, factory, version);
	}

	public DBHelper(Context context, String name, int version)
	{
		this(context, name, null, version);
	}

	public DBHelper(Context context, String name)
	{
		this(context, name, VERSION);
	}

	public DBHelper(Context context)
	{
		this(context, NAME, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{

		db.execSQL(SOLO_VOICE_SQL);
		db.execSQL(ASK_FOR_FRIEND_SQL);
		db.execSQL(MULT_CHAT_INVITE_SQL);
		db.execSQL(MULT_CHAT_MESSAGE_SQL);
		db.execSQL(VOICE_FILE_SQL);
		db.execSQL(MULT_RESULT_SQL);
		db.execSQL(New_Msg_SQL);
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2)
	{
		// TODO Auto-generated method stub

	}

	// public List<VoiceRecogListBean> getVoiceRecogList()
	// {
	// List<VoiceRecogListBean> list = new ArrayList<VoiceRecogListBean>();
	// mDB = this.getReadableDatabase();
	//
	// Cursor cursor = mDB.rawQuery("select * from " + TABLE_NAME + " order by "
	// + ID + " desc",
	// null);
	// while (cursor.moveToNext())
	// {
	//
	// Log.d(this.getClass().toString(), "content--->" + cursor.getString(2) +
	// "time->>"
	// + cursor.getString(1) + "id---->" + cursor.getInt(0));
	// list.add(new VoiceRecogListBean(cursor.getString(2), cursor.getString(1),
	// cursor
	// .getInt(0)));
	// }
	// mDB.close();
	// return list;
	// }

	public void insertVoiceContent(String content, String time)
	{
		if (mDB == null)
		{
			mDB = this.getWritableDatabase();
		}

		ContentValues values = new ContentValues();

		values.put(TIME, time);
		values.put(CONTENT, content);
		mDB.insert(TABLE_NAME, null, values);
	}

	public void closeDBHelper()
	{
		if (mDB != null)
		{
			mDB.close();
		}
		this.close();
	}

	// public void deleteContent(List<VoiceRecgListActivity.IDPair> idList)
	// {
	// mDB = getWritableDatabase();
	// for (VoiceRecgListActivity.IDPair p : idList)
	// {
	// mDB.delete(TABLE_NAME, ID + " = ?", new String[] { p.getId() });
	// }
	// mDB.close();
	//
	// }

	public void insertFriendAsk(String from, String to)
	{
		mDB = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(ASK_FOR_FRIEND_TABLE_FROM, from);
		values.put(ASK_FOR_FRIEND_TABLE_TO, to);
		values.put(ASK_FOR_FRIEND_TABLE_STATUS, ASK_FRIEND);
		values.put(ASK_FOR_FRIEND_TABLE_TAG, UN_SOLVE);
		mDB.insert(ASK_FOR_FRIEND_TABLE_NAME, null, values);
	}

	public List<FriendsAskBean> getFriendAsk()
	{

		List<FriendsAskBean> list = new ArrayList<FriendsAskBean>();

		mDB = this.getReadableDatabase();
		Cursor cursor = mDB.rawQuery("select * from "
				+ ASK_FOR_FRIEND_TABLE_NAME + " order by "
				+ ASK_FOR_FRIEND_TABLE_ID + " desc", null);
		while (cursor.moveToNext())
		{
			list.add(new FriendsAskBean(cursor.getInt(0), cursor.getString(1),
					cursor.getString(2), cursor.getInt(3), cursor.getInt(4)));
		}
		mDB.close();
		return list;
	}

	// 因为所有消息都为同一个表格，所以，此处更新处理在群组未处理时可用，也可指在好友変更已处理时可用
	public void upDateFriendsAsk(int id)
	{
		mDB = this.getWritableDatabase();

		ContentValues v = new ContentValues();
		v.put(NEW_MSG_msgStatus, NewMsgBean.Status.SOLVE);
		mDB.update(NEW_MSG_TABLE, v, NEW_MSG_id + "=?",
				new String[] { id + "" });
		mDB.close();
	}

	public void insertMultChatInvite(String roomname)
	{

		mDB = getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put(ROOM_NAME, roomname);

		values.put(MULT_CHAT_INVITE_TIME, new SimpleDateFormat(
				"yyyy-MM-dd HH:mm").format(new Date()));
		values.put(MULT_CHAT_INVITE_STATUS, UN_SOLVE);
		mDB.insert(MULT_CHAT_INVITE_TABLE_NAME, null, values);
		mDB.close();
	}

	public List<InviteMultBean> getAllInviteMultChat()
	{

		List<InviteMultBean> list = new ArrayList<InviteMultBean>();
		mDB = getReadableDatabase();

		Cursor cursor = mDB.rawQuery("select * from "
				+ MULT_CHAT_INVITE_TABLE_NAME + " order by "
				+ MULT_CHAT_INVITE_TABLE_ID + " desc", null);

		while (cursor.moveToNext())
		{
			list.add(new InviteMultBean(cursor.getInt(0), cursor.getString(1),
					cursor.getString(2), cursor.getInt(3)));
		}

		mDB.close();
		return list;
	}

	public void solveInviteMultChat(int id)
	{

		mDB = this.getWritableDatabase();

		ContentValues v = new ContentValues();
		v.put(MULT_CHAT_INVITE_STATUS, SOLVE);

		mDB.update(MULT_CHAT_INVITE_TABLE_NAME, v, MULT_CHAT_INVITE_TABLE_ID
				+ "=?", new String[] { id + "" });

		mDB.close();
	}

	public boolean insertMultChatMsg(String roomName, String msg, String from,
			String date)
	{

		mDB = getWritableDatabase();

		ContentValues value = new ContentValues();
		value.put(MULT_CHAT_INFO_TIME, date);
		value.put(MULT_CNAT_INFO_CONFERE_ROOM_NAME, roomName);
		value.put(MULT_CNAT_INFO_MESSAGE, msg);
		value.put(MULT_CHAT_INFO_FROM, from);

		SmackClient sc = SmackClient.getInstance();

		boolean isfrom = false;
		String userAccount = sc.getAccount();
		if (from.endsWith(userAccount))// 当当前用户的名称等于来的人的名称时候,这说明是要往外发生的啦
		{
			value.put(MULT_CNAT_INFO_FROM_TO, TO);// 往外发送的消息啦

		}
		else
		{
			value.put(MULT_CNAT_INFO_FROM_TO, COME);// 别人发送给你的的消息啦
			isfrom = true;
		}

		mDB.insert(MULT_CNAT_INFO_TABLE_NAME, null, value);
		mDB.close();
		return isfrom;

	}

	public List<ChatMsgBean> selectTop5Mult(String roomName)
	{

		List<ChatMsgBean> list = new ArrayList<ChatMsgBean>();

		mDB = this.getReadableDatabase();
		Cursor cursor = mDB.rawQuery("select * from "
				+ MULT_CNAT_INFO_TABLE_NAME + " where "
				+ MULT_CNAT_INFO_CONFERE_ROOM_NAME + " =\"" + roomName
				+ "\" order by " + MULT_CNAT_INFO_ID + " desc limit 0 , 5",
				null);

		while (cursor.moveToNext())
		{
			list.add(new ChatMsgBean(cursor.getInt(0), cursor.getString(2),
					cursor.getString(4), cursor.getString(3), null, (cursor
							.getInt(5) == TO ? false : true)));
		}
		mDB.close();
		return list;

	}

	public void insertVoiceFile(String fileName, String dur)
	{
		mDB = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(VOICE_FILE_TIME,
				new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()));
		values.put(VOICE_FILE_NAME, fileName);
		values.put(VOICE_LENGTH, dur);

		mDB.insert(VOICE_FILE_TABLE_NAME, null, values);
		mDB.close();
	}

	public void insertVoiceFile(String fileName, String dur, int type)
	{
		mDB = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(VOICE_FILE_TIME,
				new SimpleDateFormat("yyyy-MM-dd HH:mm").format(new Date()));
		values.put(VOICE_FILE_NAME, fileName);
		values.put(VOICE_LENGTH, dur);

		values.put(VOICE_TYPE, type);
		mDB.insert(VOICE_FILE_TABLE_NAME, null, values);
		mDB.close();
	}

	public List<VoiceFileBean> getVoiceFileList()
	{

		List<VoiceFileBean> list = new ArrayList<VoiceFileBean>();

		mDB = this.getReadableDatabase();

		Cursor cursor = mDB.rawQuery("select * from " + VOICE_FILE_TABLE_NAME
				+ " order by " + VOICE_ID + " desc", null);

		while (cursor.moveToNext())
		{
			list.add(new VoiceFileBean(cursor.getInt(0), cursor.getString(1),
					cursor.getString(2), cursor.getString(3), cursor
							.getString(4), cursor.getString(5), cursor
							.getInt(6)));

		}

		this.mDB.close();
		return list;

	}

	public void deleteVoiceFile(int postion)
	{

		mDB = getWritableDatabase();
		mDB.delete(VOICE_FILE_TABLE_NAME, VOICE_ID + " = ?",
				new String[] { postion + "" });
		mDB.close();

	}

	public void insertMultResultFile(String qunzu, String fileName)
	{
		mDB = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(MULT_RESULT_TIME, MD5Util.getNowTime());
		values.put(MULT_RESULT_FILE, fileName);
		values.put(MULT_RESULT_QZ, qunzu);
		mDB.insert(MULT_RESULT_TABLE_NAME, null, values);
		mDB.close();
	}

	public List<MultResultBean> selectMultResultFile()
	{
		List<MultResultBean> list = new ArrayList<MultResultBean>();

		mDB = this.getReadableDatabase();
		Cursor cursor = mDB.rawQuery("select * from " + MULT_RESULT_TABLE_NAME
				+ " order by " + MULT_RESULT_ID + " desc  ", null);

		while (cursor.moveToNext())
		{
			list.add(new MultResultBean(cursor.getInt(0), cursor.getString(1),
					cursor.getString(2), cursor.getString(3)));
		}
		mDB.close();
		return list;
	}

	public List<NewMsgBean> selectMsg()
	{

		List<NewMsgBean> list = new ArrayList<NewMsgBean>();

		mDB = this.getReadableDatabase();
		String sql = "select * from " + NEW_MSG_TABLE + " order by "
				+ NEW_MSG_id + " desc  ";
		Cursor cursor = mDB.rawQuery(sql, null);

		NewMsgBean bean;

		while (cursor.moveToNext())
		{
			bean = new NewMsgBean();

			bean.setId(cursor.getInt(0));
			bean.setUserid(cursor.getString(1));
			bean.setHead(cursor.getString(2));
			bean.setFromFriend(cursor.getString(3));
			bean.setToFriend(cursor.getString(4));
			bean.setRoom(cursor.getString(5));
			bean.setMsgtype(cursor.getInt(6));
			bean.setTime(cursor.getString(7));
			bean.setMsgStatus(cursor.getInt(8));
			list.add(bean);
		}
		mDB.close();
		return list;

	}

	public void deleteAllLuYin()
	{
		mDB = getWritableDatabase();

		mDB.execSQL("delete  from " + VOICE_FILE_TABLE_NAME);
		mDB.close();

	}
}
